package weifei.web.mapper;


import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;


@Mapper
public interface DBMapper
{

    @Select("${sql}")
    public List<Map<String, Object>> selectSQL(String sql);
    @Insert("${sql}")
    public int insertSQL(String sql);
    @Update("${sql}")
    public int updateSQL(String sql);
    @Delete("${sql}")
    public int deleteSQL(String sql);


    @Select("SELECT table_name FROM information_schema.tables WHERE TABLE_SCHEMA='live';")
    public List<String> selectAllTableNameList();

    @Select("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='live' AND TABLE_NAME=#{tableName};")
    public List<String> selectAllTableColumnNameList(String tableName);


    /**
     * +-----------------+-----------------------+------------------------+<br>
     * | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |<br>
     * +-----------------+-----------------------+------------------------+<br>
     * | PRIMARY         | NULL                  | NULL                   |<br>
     * +-----------------+-----------------------+------------------------+<br>
     * <br><br>
     * +-----------------+-----------------------+------------------------+<br>
     * | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |<br>
     * +-----------------+-----------------------+------------------------+<br>
     * | user_ibfk_1     | avatar                | id                     |<br>
     * +-----------------+-----------------------+------------------------+<br>
     * */
    @Select("SELECT CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='live' AND TABLE_NAME=#{tableName} AND COLUMN_NAME=#{columnName};")
    public Map<String, Object> selectColumnKeyUsage(String tableName, String columnName);


    @Select("SELECT COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='live' AND TABLE_NAME=#{tableName} AND COLUMN_NAME=#{columnName};")
    public String selectColumnDefaultValue(String tableName, String columnName);


    /**
     * @return YES 或者 NO
     * */
    @Select("SELECT IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='live' AND TABLE_NAME=#{tableName} AND COLUMN_NAME=#{columnName};")
    public String selectColumnIsNullable(String tableName, String columnName);

    @Select("SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='live' AND TABLE_NAME=#{tableName} AND COLUMN_NAME=#{columnName};")
    public String selectColumnDataType(String tableName, String columnName);



    /**
     * @return 返回 table 记录数
     * */
    @Select("SELECT COUNT(*) FROM ${tableName}")
    public int selectCount(String tableName);


    @Select("SELECT * FROM ${tableName} WHERE id=#{id}")
    public Map<String, Object> selectByID(String tableName, int id);


    @Select("SELECT * FROM ${tableName} LIMIT ${startIndex},${count};")
    public List<Map<String, Object>> selectLimit(String tableName, int startIndex, int count);

    @Select("SELECT * FROM ${tableName} WHERE ${columnName} like #{value}")
    public List<Map<String, Object>> searchByColumnValue(String tableName, String columnName, String value);


    public int insert(String tableName, @Param("map") Map<String, Object> map);
    public int updateByID(String tableName, int id, @Param("map") Map<String, Object> map);
    @Delete("DELETE FROM ${tableName} WHERE id=#{id};")
    public int deleteByID(String tableName, int id);
}


